package tgc.edu.yzy.jdbc.score;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.springframework.util.StringUtils;

import tgc.edu.yzy.bean.OpenLayout;
import tgc.edu.yzy.custom.TimeUtils;
import tgc.edu.yzy.jdbc.entity.ScoreForm;
import tgc.edu.yzy.jdbc.entity.ScoureUserForm;

public class Score {
	
	/***
	 * 获取玩家积分
	 * @param code QQ
	 * @return
	 */
	public static String get(String QQ) {
		Connection conn = ScoreConn.conn();
		String reScore = "0";
		String sql = "select number from score where qq_num='"+QQ+"'";
		PreparedStatement pr;
		try {
			pr = conn.prepareStatement(sql);
			ResultSet ex = pr.executeQuery();
			if(ex.next()) {
				reScore = ex.getString(1);
			}
			ex.close();
			pr.close();
			conn.close();
		} catch (SQLException e) {
			reScore = "?";
			e.printStackTrace();
		}
		return reScore;
	}
	/***
	 * 
	 * @param qq QQ号
	 * @param sum 分值
	 * @param type true 加法 false 减法
	 * @return 0出现错误 1积分不够 2成功
	 */
	public static Integer update(String qq,Integer sum,Boolean type,String gongneng) {
		Integer su = 0;
		Connection conn = ScoreConn.conn();
		Integer Score_Num = 0;//分数
		String sql = "select * from score where qq_num='"+qq+"'";
		PreparedStatement pr;
		try {
			pr = conn.prepareStatement(sql);
			ResultSet ex = pr.executeQuery();
			ScoreForm sc = new ScoreForm();
			if(ex.next()) {
				sc.setId(ex.getInt("id"));
				sc.setCreateName(ex.getString("create_name"));
				sc.setCreateDate(ex.getString("create_date"));
				sc.setQqNum(ex.getString("qq_num"));
				Score_Num = ex.getInt("number");
				
			}
			if(type) {
				Score_Num  += sum;
				if(!StringUtils.isEmpty(sc.getId())) {
					sql  ="update score set number='"+Score_Num+"' where qq_num='"+qq+"' and id='"+sc.getId()+"'";
					pr = conn.prepareStatement(sql);
					pr.execute();
				}else {
					sql  ="INSERT INTO score VALUES (null,'"+TimeUtils.getStringDate()+"',null,'"+Score_Num+"','"+qq+"')";
					pr = conn.prepareStatement(sql);
					pr.execute();
				}
				su = 2;
			}else {
				Score_Num -= sum;
				if(Score_Num<0||sum<0) {
					su = 1;
				}else {
					if(!StringUtils.isEmpty(sc.getId())) {
						sql  ="update score set number='"+Score_Num+"' where qq_num='"+qq+"' and id='"+sc.getId()+"'";
						pr = conn.prepareStatement(sql);
						pr.execute();
					}else {
						sql  ="INSERT INTO score VALUES (null,'"+TimeUtils.getStringDate()+"',null,'"+Score_Num+"','"+qq+"')";
						pr = conn.prepareStatement(sql);
						pr.execute();
					}
					su = 2;
				}
			}
			if(su==2) {
				String Log_Number = type?("+"+String.valueOf(sum)):("-"+sum);
				sql  ="INSERT INTO score_log VALUES (null,'"+TimeUtils.getStringDate()+"',null,'"+Log_Number+"','"+qq+"','"+gongneng+"')";
				pr = conn.prepareStatement(sql);
				pr.execute();
			}
			ex.close();
			pr.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return su;
	}
	
	/***
	 * 反馈商品信息
	 * @param id
	 * @return
	 */
	public static ScireStore getScireStore(String id) {
		String sql  = "select * from scire_store where id="+id;
		ScireStore ScireStore = new ScireStore();
		Connection conn = ScoreConn.conn();
		try {
			PreparedStatement pr = conn.prepareStatement(sql);
			ResultSet executeQuery = pr.executeQuery();
			if(executeQuery.next()) {
				ScireStore.setId(executeQuery.getInt("id"));
				ScireStore.setName(executeQuery.getString("name"));
				ScireStore.setNameRemark(executeQuery.getString("name_remark"));
				ScireStore.setUnit(executeQuery.getString("unit"));
				ScireStore.setNumber(executeQuery.getInt("number"));
				executeQuery.close();
				pr.close();
				conn.close();
				return ScireStore;
			}
			executeQuery.close();
			pr.close();
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
	/***
	 * 更新 用户数据
	 * 这里是修改用户可以添加机器人的数量
	 * @param qqcode 操作者QQ
	 * @param type 类型 qq(好友上限)
	 * @param num
	 * @return
	 */
	public static Boolean updateScoureUser(String qqcode,String type,Integer num) {
		Connection conn = ScoreConn.conn();
		String sql ="select * from score_user where qqnumber='"+qqcode+"' and type='"+type+"'";
		try {
			PreparedStatement pr = conn.prepareStatement(sql);
			ResultSet eq = pr.executeQuery();
			ScoureUserForm  form = new ScoureUserForm();
			if(eq.next()) {
				form.setId(eq.getInt("id"));
				form.setDatasum(eq.getString("datasum"));
				form.setQqnumber(eq.getString("qqnumber"));
				form.setType(eq.getString("type"));
			}
			if(!StringUtils.isEmpty(form.getId())) {
				String datasum = form.getDatasum();
				Integer Score_Num = Integer.parseInt(datasum) +num;
				sql  ="update score_user set datasum='"+Score_Num+"' where qqnumber='"+qqcode+"' and type='"+type+"'";
				pr = conn.prepareStatement(sql);
				pr.execute();
			}else {
				Integer Score_Num = 2 +num;
				sql  ="INSERT INTO score_user VALUES (null,'"+TimeUtils.getStringDate()+"',null,'"+Score_Num+"','"+qqcode+"','"+type+"')";
				pr = conn.prepareStatement(sql);
				pr.execute();
			}
			eq.close();
			pr.close();
			conn.close();
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return false;
	}
	/***
	 * 获取玩家可以添加几个机器人
	 * @param type
	 * @return
	 */
	public static Integer getScoureUser(String type,String qqcode) {
		Integer remsg = 2;
		String sql = "select * from score_user where type='"+type+"' and qqnumber='"+qqcode+"'";
		Connection conn = ScoreConn.conn();
		PreparedStatement pr;
		try {
			pr = conn.prepareStatement(sql);
			ResultSet eq = pr.executeQuery();
			if(eq.next()) {
				int datasum = eq.getInt("datasum");
				eq.close();
				pr.close();
				conn.close();
				return datasum;
			}
			eq.close();
			pr.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return remsg; 
	}
	/***
	 * 查询是否存在这个QQ群
	 * @return
	 */
	public static ScoureUserForm updateGroupIsNull(String qqcode) {
		Connection conn = ScoreConn.conn();
		String sql ="select * from score_user where datasum='"+qqcode+"' and type='group'";
		try {
			ScoureUserForm form = new ScoureUserForm();
			PreparedStatement pr = conn.prepareStatement(sql);
			ResultSet executeQuery = pr.executeQuery();
			if(executeQuery.next()) {
				form.setDatasum(executeQuery.getString("datasum"));
				form.setId(executeQuery.getInt("id"));
				form.setCreateName(executeQuery.getString("create_name"));
				form.setType(executeQuery.getString("type"));
				form.setQqnumber(executeQuery.getString("qqnumber"));
				executeQuery.close();
				pr.close();
				conn.close();
				return form;
			}
			executeQuery.close();
			pr.close();
			conn.close();
			return null;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
		
	}
	/***
	 * 更新 用户 QQ群专用
	 * 这里是修改用户可以添加机器人的数量
	 * @param qqcode 操作者QQ
	 * @param type 类型 qq(好友上限)
	 * @param creName 
	 * @param num
	 * @return
	 */
	public static Boolean updateScoureUserGroup(String qqcode,String type,String group, String creName) {
		Connection conn = ScoreConn.conn();
		try {
			
			ScoureUserForm updateGroupIsNull = updateGroupIsNull(group);
			String sql  ="";
			if(updateGroupIsNull!=null) {
				sql  ="update score_user set create_name='"+creName+"',qqnumber='"+qqcode+"' where datasum='"+qqcode+"' and type='"+type+"'";
			}else {
				sql  ="INSERT INTO score_user VALUES (null,'"+TimeUtils.getStringDate()+"','"+creName+"','"+group+"','"+qqcode+"','"+type+"')";
			}
			PreparedStatement prepareStatement = conn.prepareStatement(sql);
			prepareStatement.execute();
			prepareStatement.close();
			conn.close();
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return false;
	}
	/***
	 * 更新 用户 QQ群专用
	 * 这里是修改用户可以添加机器人的数量
	 * @param qqcode 操作者QQ
	 * @return
	 */
	public static List<String> getScoreUser(String qqcode) {
		Connection conn = ScoreConn.conn();
		List<String> relust = new ArrayList<String>();
		try {
			String sql   ="select * from score_user where qqnumber='"+qqcode+"' and type='group'";
			PreparedStatement prepareStatement = conn.prepareStatement(sql);
			ResultSet eq = prepareStatement.executeQuery();
			while(eq.next()) {
				String data = eq.getString("datasum");
				String create_name = eq.getString("create_name");
				if("永久授权".equals(create_name)) {
					data+="(永久)";
				}
				relust.add(data);
			}
			prepareStatement.close();
			conn.close();
			return relust;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return relust;
	}
	/***
	 * 删除一次性同意进入的群
	 * @param group
	 * @return
	 */
	public static boolean deleteGroup(String group) {
		Connection conn = ScoreConn.conn();
		try {
			String sql  ="DELETE FROM  score_user where datasum='"+group+"' and type='group'";
			PreparedStatement prepareStatement = conn.prepareStatement(sql);
			prepareStatement.execute();
			prepareStatement.close();
			conn.close();
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return false;
	}
}
